<?php

namespace app\index\controller;

use app\common\controller\Frontend;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use think\Db;

class Index extends Frontend
{

    protected $noNeedLogin = '*';
    protected $noNeedRight = '*';
    protected $layout = '';

    public function index()
    {
        return $this->view->fetch();
    }

    public function excel(){
        //1.获取文件后缀
        //2.按照不同的后缀载入不同的excel 数据读取器
        //3.定位sheet1
        //4.froeach 从第4行读取到81行
        //5.打印各列数据
        //C:\phpStudy\PHPTutorial\WWW\fast.io\public\uploads\user\98人武威m.xlsx
        $filename="uploads/user/98人武威m.xlsx";
        $ext=substr($filename,strpos($filename,'.'));

        if ($ext=='.xlsx'){
            //excel 2007
            $reader=IOFactory::createReader('Xlsx');
            $reader->setReadDataOnly(true);
            $spreadsheet=$reader->load($filename);
            $worksheet=$spreadsheet->getActiveSheet();

            $datasheet=[];
            /*echo '<table>' . PHP_EOL;
            foreach ($worksheet->getRowIterator() as $key=>$row) {
                if($key>3){
                    echo '<tr>' . PHP_EOL;
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
                    foreach ($cellIterator as $k=>$cell) {
                        //echo '<td>' . $cell->getValue() . '</td>' . PHP_EOL;

                        $datasheet[$key][$k]=$cell->getValue();
                    }
                    echo '</tr>' . PHP_EOL;
                }
            }
            echo '</table>' . PHP_EOL;*/

            foreach($worksheet->getRowIterator() as $key=>$row){
                if ($key>3){
                    $cellIterator=$row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(false);
                    foreach ($cellIterator as $k=>$cell){
                        $datasheet[$key][$k]=$cell->getValue();
                    }
                }
            }

            print_r($datasheet);
            Db::name('work')->insertAll($datasheet);
            echo "插入成功";

        }elseif ($ext=='xls'){
            //excel 2003
        }else{
            $this->error('非法的文件');
        }

    }

}
